package com.xj.dao;

import com.xj.entity.InvUpdateInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

public interface InvUpdateInfoDao extends JpaRepository<InvUpdateInfo, Integer> {

    @Query(value = "select sum(isnull(quantity,0)) as  quantity   from (" +
            "  select  case  when   propertyname in ('MaterialForSend','OtherForDispatch') " +
            " then  s.basequantity*(-1)  else s.basequantity end     as quantity " +
            "   from  ST_NewCurrentStock  s " +
            "   inner join aa_inventory i  on  s.idinventory=i.id " +
            " where   s.idwarehouse in (30,11) and   propertyname not in ('SaleDelivery') " +
            " and  i.code=:code  " +
            " union all  select case when  s.  propertyname in ('MaterialForSend','OtherForDispatch') then  (-1)* s.basequantity else s.basequantity end " +
            "   from   ST_NewCurrentStock_FlowRecord  s " +
            "   inner join aa_inventory i  on  s.idinventory=i.id " +
            "   where   s.idwarehouse in (30,11) " +
            "    and s.propertyname not   in ('SaleDelivery','PurchaseArrival') " +
            "   and  i.code=:code )v ",nativeQuery = true)
    public BigDecimal getQuantityBycode(String code);

     @Query(value = " select   case  when  isnull(m.Unit1ChangeRate,0)=0  then  1 else  cast(1/m.Unit1ChangeRate as decimal(18,0))   end  as rate," +
             "e.code  as invcode  from  AA_Inventory_MultiUnit  m  " +
             " inner  join  AA_InventoryEntity  e  on  m.idinventory  =e.id " +
             " where  isnull(e.priuserdefdecm1,0) <>case  when  isnull(m.Unit1ChangeRate,0)=0  then  1 else  cast(1/m.Unit1ChangeRate as decimal(18,0))   end ",nativeQuery = true)
  List<Map<String,String>>  getInvUnitRate();

     @Modifying
     @Transactional
     @Query(value = "update  mpb  set  mpb.priuserdefdecm1=case when  DATEDIFF(day,mpb.preFinishDate, ISNULL(mpb.finishdate,isnull(mpb.ClosedDate, getdate())))<0 " +
             "then  0  else  DATEDIFF(day,mpb.preFinishDate, ISNULL(mpb.finishdate, isnull(mpb.ClosedDate,getdate()))) end " +
             "from  dbo.MP_ManufactureOrder_b mpb inner join MP_ManufactureOrder  mp  on  mp.id=mpb.idManufactureOrderDTO " +
             " where    mp.voucherstate>181 and  isnull(mpb.priuserdefdecm1,0)<>case when  DATEDIFF(day,mpb.preFinishDate, ISNULL(mpb.finishdate,isnull(mpb.ClosedDate, getdate())))<0 then  0" +
             "  else  DATEDIFF(day,mpb.preFinishDate, ISNULL(mpb.finishdate, isnull(mpb.ClosedDate,getdate()))) end",nativeQuery = true)
     int   updateDelayDay();
}


